package org.bizbundles.forward;

import jxl.*;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.bizbundles.support.FileNameHelper;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Copyright 2007 BizBundle.org
 * <p/>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p/>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p/>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * <p/>
 * This class will convert an MS Excel Spreadsheet into resource bundles
 * one for each tab in the excel spreadsheet
 *
 * @author Steve Roach
 * @since: Nov 1, 2007
 * 7:17:54 PM
 *
 * todo display duplicate keys
 */
public class ExcelToResourceBundle
{
    private final Log logger = LogFactory.getLog(ExcelToResourceBundle.class);

    private static final List<String> DATE_FORMAT_LIST = Arrays.asList("dd/mm/yyyy", "None", "Solid");
    private static final String PROPERTIES_EXTENSION = ".properties";
    private List<Set<String>> listOfKeys = new LinkedList<Set<String>>();
    private MetaInfo metaInfo = new MetaInfo();

    public void generateResources(File excelFile)
    {
        assert excelFile != null : "The excel file passed in was NULL";
        assert excelFile.exists() : "file does not exists.";
        assert excelFile.isFile() : "The excelFile " + excelFile.getName() + " is not a file, it i a directory.";
        assert excelFile.canRead() : "Unable to read file";

        File generated = new File(excelFile.getParent() + File.separator + "generated" + FileNameHelper.getDateTimeString());
        generated.mkdir();
        try
        {
            Workbook workbook = Workbook.getWorkbook(excelFile);
            String[] sheetNames = workbook.getSheetNames();
            if (logger.isInfoEnabled())
            {
                logger.info("Total of " + sheetNames.length + " worksheets found in excel file");
            }
            for (String sheetName : sheetNames)
            {
                convertSheetToMap(generated, workbook, sheetName);
            }
            workbook.close();
            warnMatchingNumberOfKeys();
            metaInfo.showKeyInfo();
        }
        catch (Exception e)
        {
            throw new RuntimeException(e);
        }
    }

    /**
     * reads each sheet and store it in a Map in memory
     * @param generated pointer to generated File object
     * @param workbook pointer to jxl workbook
     * @param sheetName the name of the sheet to copy from
     * @throws IOException exception for IO operations
     */
    private void convertSheetToMap(File generated, Workbook workbook, String sheetName) throws IOException
    {
        Sheet sheet = workbook.getSheet(sheetName);
        Map<String, String> props = new HashMap<String, String>();
        int maxRows = sheet.getRows();
        for (int i = 0; i < maxRows; i++)
        {
            Cell cell1 = sheet.getCell(0, i);
            Cell cell2 = sheet.getCell(1, i);
            Cell cell3 = sheet.getCell(2, i);
            String cell0Text = getCellContent(cell1);
            String cell1Text = getCellContent(cell2);
            String cell3Text = getCellContent(cell3);
            if (!"N".equalsIgnoreCase(cell3Text))
            {
                props.put(cell0Text, cell1Text);
            }
        }
        storeMapAsResourceBundle(generated, sheetName, props);
    }

    /**
     * private method to copy the in memory map into a resource bundle using a Properties class
     * @param generated pointer to generated File object
     * @param sheetName the name of the sheet to name the resource bundle with.
     * @param props the map of all properties
     * @throws IOException any io operations issues while creating resource bundle
     */
    private void storeMapAsResourceBundle(File generated, String sheetName, Map<String, String> props) throws IOException
    {
        Set<String> set = new TreeSet<String>(props.keySet());
        listOfKeys.add(set);
        Properties properties = new Properties();
        properties.putAll(props);
        if (sheetName.endsWith(PROPERTIES_EXTENSION))
        {
            sheetName = StringUtils.remove(sheetName, PROPERTIES_EXTENSION);
        }
        properties.store(new FileOutputStream(generated.getAbsolutePath() + File.separator + sheetName + PROPERTIES_EXTENSION), "Generated By org.bizbundles.ExcelToResourceBundle");
        metaInfo.add(sheetName, props.keySet());
    }


    private String getCellContent(Cell currCell)
    {
        String cellText;
        if (null != currCell && CellType.DATE.equals(currCell.getType()))
        {
            String formatPattern = currCell.getCellFormat().getPattern().getDescription();
            if (DATE_FORMAT_LIST.contains(formatPattern))
            {
                TimeZone gmtZone = TimeZone.getTimeZone("GMT");
                SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy");
                format.setTimeZone(gmtZone);
                DateCell dateCell = (DateCell) currCell;
                cellText = format.format(dateCell.getDate());
            }
            else
            {
                cellText = currCell.getContents();
                cellText = cellText.replace('"', ' ');
            }
        }
        else if (null != currCell && CellType.NUMBER.equals(currCell.getType()))
        {
            if ((currCell.getCellFormat().getFormat().getFormatString()).startsWith("[<=9999999]"))
            {
                String val = StringUtils.remove(currCell.getContents(), "[<=9999999]");
                val = StringUtils.remove(val, '-');
                cellText = val;
            }
            else
            {
                cellText = currCell.getContents();
                cellText = cellText.replace('"', ' ');
            }
        }
        else
        {
            cellText = currCell != null ? currCell.getContents() : "";
            cellText = cellText.replace('"', ' ');
        }
        //logger.info(cellText);
        return cellText.trim();
    }

    private void warnMatchingNumberOfKeys()
    {
        boolean equals = true;
        if (listOfKeys.size() == 1)
        {
            equals = true;
        }
        else
        {
            Set<String> set1 = listOfKeys.get(0);
            Set<String> other;
            for (int i = 1; i < listOfKeys.size(); i++)
            {
               other = listOfKeys.get(i);
               if(!set1.equals(other))
               {
                   equals = false;
               }
            }
        }
        if(!equals)
        {
            logger.warn("There are keys that do not have an equivalent in other sheets");
        }
    }

    private class MetaInfo
    {
        private Map<String, Integer> totalKeysPerSheet = new HashMap<String, Integer>();

        private void add(String sheetName, Set<String> keys)
        {
            totalKeysPerSheet.put(sheetName, keys.size());
        }

        public Map<String, Integer> getTotalKeysPerSheet()
        {
            return totalKeysPerSheet;
        }
        private void showKeyInfo()
        {
            for (String sheetName : totalKeysPerSheet.keySet())
            {
                logger.info("Sheet " + sheetName + " has " + totalKeysPerSheet.get(sheetName) + " number of resource entries.");
            }
        }
    }
}
